CREATE procedure [dbo].[sp_asi_Cert_Completion]
@ProgID varchar (31),
@NotifyBoard bit=0,
@NotifyStudent bit=0,
@ActUF1 varchar(255)='',
@ActUF2 varchar(255)='',
@ActUF3 varchar(255)='',
@ActUF4 float=0,
@ActUF5 float=0,
@ActUF6 datetime=null,
@ActUF7 datetime=null,
@AllowGracePeriods bit=0,
@UpdateGoodThruForGracePeriod bit=0,
@CurrentLocalDate varchar (50)=''
as
set nocount on
declare
@RowsAffected integer,
@Seqn int,
@StudentID varchar (10),
@RegistrationItem varchar (31),
@Status varchar (1),
@EnrolledDate datetime,
@Deadline datetime,
@CompletionDate datetime,
@BoardNotified bit,
@StudentNotified bit,
@GoodThruDate datetime,
@RequirementType int,
@InGracePeriod bit,
@ActivitySeqn int,
@GracePeriodMonths int,
@GracePeriodForInit bit,
@GracePeriodForMaint bit,
@Designation1 varchar (20),
@Designation2 varchar (20),
@Designation3 varchar (20),
@JointProgram varchar (31),
@RotateDateRule int,
@RotateBeginDate datetime,
@RotateCompletionMonths int,
@FullName varchar (60),
@Company varchar (80),
@City varchar (40),
@StateProvince varchar (15),
@Country varchar (25),
@ProgramID varchar (31),
@Title varchar (50),
@AutoEnrollForMaint bit,
@ProgramType int,
@CurrentProgramID varchar (31),
@JointProgramProof bit,
@CurrentProgramProof bit,
@Seqn1 int,
@Seqn2 int,
@SuperCategory bit,
@SubcategoryNames varchar(8000),
@CplProgramID varchar (31),
@CplComponentCode varchar (31),
@CplComponentCategory varchar (31),
@CplLineNumber int,
@CplLineType varchar (10),
@CplUnitsRequiredForCredit numeric(15,2),
@CplRequirementType int,
@ProductCertParent bit,
@CertRegUnitsEarned numeric(15,2),
@CertRegSeqn int,
@CertRegRegType varchar (1),
@CertRegProgID varchar(31),
@TotalUnits numeric(15,2),
@UnitsFromCPL numeric(15,2),
@UnitsFromComponents numeric(15,2),
@UnitsFromSubComponents numeric(15,2),
@UnitsFromSubCategories numeric(15,2),
@CompProdCode varchar(31),
@SubcategoryNamesItem varchar (31),
@SubCompProdCode varchar (31),
@SubCompLineNumber int,
@SubCompChildProdCode varchar (31),
@SubCompRequired bit,
@Abort bit,
@Result int,
@NewProgSeqn int,
@NewActSeqn int,
@CoID varchar (10),
@MemberType varchar (5),
@TotCharges money,
@CRNotes varchar(8000),
@ActString varchar (255),
@TreatDeadline bit,
@TotalUnitsRequiredForCredit numeric(15,2)
create table #ProgramLinesList (
PROGRAM_ID varchar (31),
COMPONENT_CODE varchar (31),
COMPONENT_CATEGORY varchar (31),
LINE_NUMBER int,
LINE_TYPE varchar (10),
UNITS_REQUIRED_FOR_CREDIT numeric(15,2),
REQUIREMENT_TYPE int
)
create table #ComponentList (
PRODUCT_CODE varchar (31)
)
create table #SubcomponentList (
PRODUCT_CODE varchar (31),
LINE_NUMBER int,
CHILD_PRODUCT_CODE varchar (31),
REQUIRED bit,
SubcomponentCompleted bit
)
create table #TTPList (
TTPComponentRegSeqn int
)
create table #CompletedList (
STUDENT_ID varchar (10),
COMPLETION_DATE datetime,
PROGRAM varchar (31),
FULLNAME varchar (60),
COMPANY varchar (80),
CITY varchar (40),
STATE_PROVINCE varchar (15),
COUNTRY varchar (25),
SEQN int,
ACTIVITY_SEQN int,
InGracePeriod bit,
AutoEnrollForMaint bit,
ProgramType int,
RotateDateRule int,
RotateBeginDate datetime,
RotateCompletionMonths int,
Designation1 varchar (20),
Designation2 varchar (20),
Designation3 varchar (20),
GracePeriodForInit bit,
GracePeriodForMaint bit,
Deadline datetime,
GoodThruDate datetime,
CoID varchar (10),
MemberType varchar (5),
TotCharges money,
CRNotes varchar(8000),
RequirementType int,
Title varchar (50)
)
truncate table #CompletedList
if @ProgID='ALL'
declare get_rows cursor for
select cr.SEQN,cr.STUDENT_ID,cr.REGISTRATION_ITEM,cr.STATUS,cr.ENROLLED_DATE,cr.TOTAL_CHARGES,cr.NOTES,
cr.DEADLINE,cr.COMPLETION_DATE,cr.BOARD_NOTIFIED,cr.GOOD_THRU_DATE,cr.REQUIREMENT_TYPE,
cr.IN_GRACE_PERIOD,cr.ACTIVITY_SEQN,cp.GRACE_PERIOD_MONTHS,cp.GRACE_PERIOD_FOR_INIT,
cp.GRACE_PERIOD_FOR_MAINT,cp.DESIGNATION_1,cp.DESIGNATION_2,cp.DESIGNATION_3,cp.JOINT_PROGRAM,
cp.AUTO_ENROLL_FOR_MAINT, cp.ROTATE_DATE_RULE,cp.ROTATE_BEGIN_DATE, cp.ROTATE_COMPLETION_MONTHS,
n.FULL_NAME,n.COMPANY,n.CITY,n.STATE_PROVINCE,n.COUNTRY,n.CO_ID,n.MEMBER_TYPE,cp.ID,cp.TITLE,cp.PROGRAM_TYPE
from Cert_Register cr,Cert_Program cp,Name n
where cr.SEQN in (select cr2.SEQN from Cert_Register cr2,Cert_Program cp2 where cr2.REGISTRATION_ITEM = cp2.ID)
and cr.STUDENT_ID=n.ID and cr.REGISTRATION_ITEM=cp.ID and cr.STATUS='E'
and cr.ENROLLED_DATE is not null and cr.CANCELED_DATE is null order by cp.ID
else
declare get_rows cursor for
select cr.SEQN,cr.STUDENT_ID,cr.REGISTRATION_ITEM,cr.STATUS,cr.ENROLLED_DATE,cr.TOTAL_CHARGES,cr.NOTES,
cr.DEADLINE,cr.COMPLETION_DATE,cr.BOARD_NOTIFIED,cr.GOOD_THRU_DATE,cr.REQUIREMENT_TYPE,
cr.IN_GRACE_PERIOD,cr.ACTIVITY_SEQN,cp.GRACE_PERIOD_MONTHS,cp.GRACE_PERIOD_FOR_INIT,
cp.GRACE_PERIOD_FOR_MAINT,cp.DESIGNATION_1,cp.DESIGNATION_2,cp.DESIGNATION_3,cp.JOINT_PROGRAM,
cp.AUTO_ENROLL_FOR_MAINT, cp.ROTATE_DATE_RULE,cp.ROTATE_BEGIN_DATE, cp.ROTATE_COMPLETION_MONTHS,
n.FULL_NAME,n.COMPANY,n.CITY,n.STATE_PROVINCE,n.COUNTRY,n.CO_ID,n.MEMBER_TYPE,cp.ID,cp.TITLE,cp.PROGRAM_TYPE
from Cert_Register cr,Cert_Program cp,Name n
where cr.SEQN in (select cr2.SEQN from Cert_Register cr2,Cert_Program cp2 where cr2.REGISTRATION_ITEM = cp2.ID and cp2.ID=@ProgID)
and cr.STUDENT_ID=n.ID and cr.REGISTRATION_ITEM=cp.ID and cr.STATUS='E' and cp.ID=@ProgID
and cr.ENROLLED_DATE is not null and cr.CANCELED_DATE is null
open get_rows
fetch next from get_rows into
@Seqn,
@StudentID,
@RegistrationItem,
@Status,
@EnrolledDate,
@TotCharges,
@CRNotes,
@Deadline,
@CompletionDate,
@BoardNotified,
@GoodThruDate,
@RequirementType,
@InGracePeriod,
@ActivitySeqn,
@GracePeriodMonths,
@GracePeriodForInit,
@GracePeriodForMaint,
@Designation1,
@Designation2,
@Designation3,
@JointProgram,
@AutoEnrollForMaint,
@RotateDateRule,
@RotateBeginDate,
@RotateCompletionMonths,
@FullName,
@Company,
@City,
@StateProvince,
@Country,
@CoID,
@MemberType,
@ProgramID,
@Title,
@ProgramType
While (@@FETCH_STATUS <> -1)
BEGIN
if @@FETCH_STATUS <> -2
BEGIN
truncate table #ProgramLinesList
truncate table #SubcomponentList
truncate table #ComponentList
truncate table #TTPList
select @TotalUnits=0
select @Abort=0
select @TotalUnitsRequiredForCredit=0
if datalength(isnull(@JointProgram,''))>0
BEGIN
select @Seqn1=0
select @Seqn2=0
select @Seqn1=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@JointProgram and STATUS='P' and GOOD_THRU_DATE>=getdate()
if isnull(@Seqn1,0)=0
BEGIN
select @Seqn1=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@JointProgram and STATUS='P'
select @Seqn2=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@JointProgram and STATUS='E' and GOOD_THRU_DATE>=getdate()
END
if isnull(@Seqn1,0)>0 and isnull(@Seqn2,0)>0
BEGIN
select @Seqn1=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@RegistrationItem and STATUS='P' and GOOD_THRU_DATE>=getdate()
if isnull(@Seqn1,0)=0
BEGIN
select @Seqn1=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@RegistrationItem and STATUS='P'
select @Seqn2=SEQN from Cert_Register where STUDENT_ID=@StudentID
and REG_TYPE='P' and REGISTRATION_ITEM=@RegistrationItem and STATUS='E' and GOOD_THRU_DATE>=getdate()
END
if isnull(@Seqn1,0)>0 and isnull(@Seqn2,0)>0
BEGIN
select @RequirementType=3
END
END
END
insert into #ProgramLinesList
select PROGRAM_ID,COMPONENT_CODE,COMPONENT_CATEGORY,LINE_NUMBER,LINE_TYPE,
UNITS_REQUIRED_FOR_CREDIT,REQUIREMENT_TYPE from Cert_Prog_Lines
where PROGRAM_ID=@ProgramID and REQUIREMENT_TYPE=@RequirementType
declare get_requirements cursor for
select * from #ProgramLinesList
open get_requirements
fetch next from get_requirements into
@CplProgramID,
@CplComponentCode,
@CplComponentCategory,
@CplLineNumber,
@CplLineType,
@CplUnitsRequiredForCredit,
@CplRequirementType
While (@@FETCH_STATUS <> -1) and @Abort=0
BEGIN
if @@FETCH_STATUS <> -2
BEGIN
if @CplUnitsRequiredForCredit>0
select @TotalUnitsRequiredForCredit=@TotalUnitsRequiredForCredit+@CplUnitsRequiredForCredit
if isnull(@CplLineType,'')='COMPONENT'
BEGIN
select @UnitsFromCPL=0
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,
@CertRegSeqn=cr.SEQN,@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CplComponentCode and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromCPL=@UnitsFromCPL+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
if isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
if isnull(@UnitsFromCPL,0)>0
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromCPL
select @UnitsFromCPL=0
END
END
ELSE
BEGIN
select @Abort=1
END
END
if isnull(@CplLineType,'')='CATEGORY'
BEGIN
select @SuperCategory=0
select @SubcategoryNames=''
select @SuperCategory=SUPER_CATEGORY,@SubcategoryNames=SUBCATEGORY_NAMES
from Cert_Comp_Cat where CATEGORY=@CplComponentCategory
if isnull(@SuperCategory,0)=1
BEGIN
select @UnitsFromSubCategories=0
if datalength(isnull(@SubcategoryNames,''))>0
BEGIN
if charindex(',',@SubcategoryNames)=0
BEGIN
select @SubcategoryNamesItem=@SubcategoryNames
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
if charindex(',',@SubcategoryNames)>0
BEGIN
while charindex(',',@SubcategoryNames)>0
BEGIN
select @SubcategoryNamesItem=substring(@SubcategoryNames,1,charindex(',',@SubcategoryNames)-1)
select @SubcategoryNames=substring(@SubcategoryNames,charindex(',',@SubcategoryNames)+1,datalength(@SubcategoryNames))
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
select @SubcategoryNamesItem=@SubcategoryNames
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
END
if isnull(@UnitsFromSubCategories,0)<isnull(@CplUnitsRequiredForCredit,0)
BEGIN
select @Abort=1
END
if isnull(@UnitsFromSubCategories,0)>0
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromSubCategories
select @UnitsFromSubCategories=0
END
END
ELSE
BEGIN
truncate table #ComponentList
select @UnitsFromComponents=0
select @UnitsFromSubComponents=0
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@CplComponentCategory and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_components cursor for
select PRODUCT_CODE from #ComponentList
open get_components
fetch next from get_components into @CompProdCode
if @@FETCH_STATUS<>0
BEGIN
select @Abort=1
END
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromComponents=@UnitsFromComponents+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
if isnull(@ProductCertParent,0)<>0
BEGIN
truncate table #SubcomponentList
insert into #SubcomponentList
select PRODUCT_CODE,LINE_NUMBER,CHILD_PRODUCT_CODE,REQUIRED,0
from Product_Sub where Product_Sub.PRODUCT_CODE=@CompProdCode
declare get_subcomponents cursor for
select @SubCompChildProdCode from #SubcomponentList
open get_subcomponents
fetch next from get_subcomponents into @SubCompChildProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @CertRegUnitsEarned=0
select @CertRegUnitsEarned=cr.UNITS_EARNED from Cert_Register cr
where cr.REGISTRATION_ITEM=@SubCompChildProdCode
and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
update #SubcomponentList set SubcomponentCompleted=1 where current of get_subcomponents
select @UnitsFromSubComponents=@UnitsFromSubComponents+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
fetch next from get_subcomponents into @SubCompChildProdCode
END
close get_subcomponents
deallocate get_subcomponents
select @UnitsFromComponents=@UnitsFromComponents+@UnitsFromSubComponents
END
if isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
END
fetch next from get_components into @CompProdCode
END
close get_components
deallocate get_components
if isnull(@UnitsFromComponents,0)>0 and (isnull(@UnitsFromComponents,0)>=isnull(@CplUnitsRequiredForCredit,0))
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromComponents
select @UnitsFromComponents=0
select @UnitsFromSubComponents=0
END
ELSE
BEGIN
select @Abort=1
END
END
END
END
fetch next from get_requirements into
@CplProgramID,
@CplComponentCode,
@CplComponentCategory,
@CplLineNumber,
@CplLineType,
@CplUnitsRequiredForCredit,
@CplRequirementType
END
close get_requirements
deallocate get_requirements
if isnull(@InGracePeriod,0)<>0 and @Abort=0
BEGIN
select @RequirementType=2
truncate table #ProgramLinesList
insert into #ProgramLinesList
select PROGRAM_ID,COMPONENT_CODE,COMPONENT_CATEGORY,LINE_NUMBER,LINE_TYPE,
UNITS_REQUIRED_FOR_CREDIT,REQUIREMENT_TYPE from Cert_Prog_Lines
where PROGRAM_ID=@ProgramID and REQUIREMENT_TYPE=@RequirementType
declare get_requirements cursor for
select * from #ProgramLinesList
open get_requirements
fetch next from get_requirements into
@CplProgramID,
@CplComponentCode,
@CplComponentCategory,
@CplLineNumber,
@CplLineType,
@CplUnitsRequiredForCredit,
@CplRequirementType
While (@@FETCH_STATUS <> -1)
BEGIN
if @@FETCH_STATUS <> -2
BEGIN
if isnull(@CplLineType,'')='COMPONENT'
BEGIN
select @UnitsFromCPL=0
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,
@CertRegSeqn=cr.SEQN,@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CplComponentCode and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromCPL=@UnitsFromCPL+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
if isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
if isnull(@UnitsFromCPL,0)>0
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromCPL
select @UnitsFromCPL=0
END
END
ELSE
BEGIN
select @Abort=1
END
END
if isnull(@CplLineType,'')='CATEGORY'
BEGIN
select @SuperCategory=0
select @SubcategoryNames=''
select @SuperCategory=SUPER_CATEGORY,@SubcategoryNames=SUBCATEGORY_NAMES
from Cert_Comp_Cat where CATEGORY=@CplComponentCategory
if isnull(@SuperCategory,0)=1
BEGIN
select @UnitsFromSubCategories=0
if datalength(isnull(@SubcategoryNames,''))>0
BEGIN
if charindex(',',@SubcategoryNames)=0
BEGIN
select @SubcategoryNamesItem=@SubcategoryNames
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
if charindex(',',@SubcategoryNames)>0
BEGIN
while charindex(',',@SubcategoryNames)>0
BEGIN
select @SubcategoryNamesItem=substring(@SubcategoryNames,1,charindex(',',@SubcategoryNames)-1)
select @SubcategoryNames=substring(@SubcategoryNames,charindex(',',@SubcategoryNames)+1,datalength(@SubcategoryNames))
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
select @SubcategoryNamesItem=@SubcategoryNames
truncate table #ComponentList
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@SubcategoryNamesItem and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_subcategories cursor for
select PRODUCT_CODE from #ComponentList
open get_subcategories
fetch next from get_subcategories into @CompProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromSubCategories=@UnitsFromSubCategories+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
END
if isnull(@CertRegSeqn,0)>0 and isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
fetch next from get_subcategories into @CompProdCode
END
close get_subcategories
deallocate get_subcategories
END
END
if isnull(@UnitsFromSubCategories,0)>0
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromSubCategories
select @UnitsFromSubCategories=0
END
ELSE
if @UnitsFromSubCategories<@CplUnitsRequiredForCredit
BEGIN
select @Abort=1
END
END
ELSE
BEGIN
truncate table #ComponentList
select @UnitsFromComponents=0
select @UnitsFromSubComponents=0
insert into #ComponentList
select p.PRODUCT_CODE from Cert_Register cr,Product_Cert pc,Product p where p.PRODUCT_CODE = pc.PRODUCT_CODE
and pc.PRODUCT_CODE=cr.REGISTRATION_ITEM and p.CATEGORY=@CplComponentCategory and p.PROD_TYPE='CERTIFY'
and pc.SUBCOMPONENT=0 and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID) and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn))
group by p.PRODUCT_CODE
declare get_components cursor for
select PRODUCT_CODE from #ComponentList
open get_components
fetch next from get_components into @CompProdCode
if @@FETCH_STATUS<>0
BEGIN
select @Abort=1
END
WHILE @@FETCH_STATUS = 0
BEGIN
select @ProductCertParent=0
select @CertRegUnitsEarned=0
select @CertRegSeqn=0
select @CertRegRegType=''
select @CertRegProgID=''
select @ProductCertParent=pc.PARENT,@CertRegUnitsEarned=cr.UNITS_EARNED,@CertRegSeqn=cr.SEQN,
@CertRegRegType=cr.REG_TYPE,@CertRegProgID=cr.PROGRAM_ID
from Product_Cert pc LEFT OUTER JOIN Cert_Register cr on pc.PRODUCT_CODE=cr.REGISTRATION_ITEM
where pc.PRODUCT_CODE=@CompProdCode and cr.STUDENT_ID=@StudentID
and ((cr.REG_TYPE='C' or cr.REG_TYPE='B') and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegSeqn,0)>0
BEGIN
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
select @UnitsFromComponents=@UnitsFromComponents+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
if isnull(@ProductCertParent,0)<>0
BEGIN
truncate table #SubcomponentList
insert into #SubcomponentList
select PRODUCT_CODE,LINE_NUMBER,CHILD_PRODUCT_CODE,REQUIRED,0
from Product_Sub where Product_Sub.PRODUCT_CODE=@CompProdCode
declare get_subcomponents cursor for
select @SubCompChildProdCode from #SubcomponentList
open get_subcomponents
fetch next from get_subcomponents into @SubCompChildProdCode
WHILE @@FETCH_STATUS = 0
BEGIN
select @CertRegUnitsEarned=0
select @CertRegUnitsEarned=cr.UNITS_EARNED from Cert_Register cr
where cr.REGISTRATION_ITEM=@SubCompChildProdCode
and cr.STUDENT_ID=@StudentID and ((cr.REG_TYPE='C' or cr.REG_TYPE='B')
and (cr.PROGRAM_ID='' or cr.PROGRAM_ID=@ProgramID)
and (cr.PROGRAM_REG_SEQN=0 or cr.PROGRAM_REG_SEQN=@Seqn)) and cr.STATUS='P'
if isnull(@CertRegUnitsEarned,0)>0
BEGIN
update #SubcomponentList set SubcomponentCompleted=1 where current of get_subcomponents
select @UnitsFromSubComponents=@UnitsFromSubComponents+@CertRegUnitsEarned
select @CertRegUnitsEarned=0
END
fetch next from get_subcomponents into @SubCompChildProdCode
END
close get_subcomponents
deallocate get_subcomponents
select @UnitsFromComponents=@UnitsFromComponents+@UnitsFromSubComponents
END
if isnull(@CertRegRegType,'')='B'
BEGIN
insert into #TTPList select @CertRegSeqn
END
END
fetch next from get_components into @CompProdCode
END
close get_components
deallocate get_components
if isnull(@UnitsFromComponents,0)>0 and (isnull(@UnitsFromComponents,0)>=isnull(@CplUnitsRequiredForCredit,0))
BEGIN
select @TotalUnits=@TotalUnits+@UnitsFromComponents
select @UnitsFromComponents=0
select @UnitsFromSubComponents=0
END
ELSE
BEGIN
select @Abort=1
END
END
END
END
fetch next from get_requirements into
@CplProgramID,
@CplComponentCode,
@CplComponentCategory,
@CplLineNumber,
@CplLineType,
@CplUnitsRequiredForCredit,
@CplRequirementType
END
close get_requirements
deallocate get_requirements
END
if @Abort=1
BEGIN
select @TotalUnits=0
select @Abort=0
END
if (@TotalUnits>0 and @TotalUnitsRequiredForCredit>0 and @TotalUnits>=@TotalUnitsRequiredForCredit) or (@TotalUnits=0 and @TotalUnitsRequiredForCredit=0) and @CertRegSeqn>0
BEGIN
select @CompletionDate=getdate()
insert into #CompletedList
select @StudentID,@CompletionDate,@ProgramID,@FullName,@Company,@City,@StateProvince,@Country,
@Seqn,@ActivitySeqn,@InGracePeriod,@AutoEnrollForMaint,@ProgramType,@RotateDateRule,@RotateBeginDate,
@RotateCompletionMonths,@Designation1,@Designation2,@Designation3,@GracePeriodForInit,@GracePeriodForMaint,
@Deadline,@GoodThruDate,@CoID,@MemberType,@TotCharges,@CRNotes,@RequirementType,@Title
update Cert_Register set PROGRAM_ID=@ProgramID,PROGRAM_REG_SEQN=@Seqn
from Cert_Register,#TTPList where SEQN=TTPComponentRegSeqn
if isnull(@NotifyBoard,0)=0 and isnull(@NotifyStudent,0)=0
BEGIN
update Cert_Register set COMPLETION_DATE=getdate(), STATUS='P' where SEQN=@Seqn
END
if isnull(@NotifyBoard,0)=0 and isnull(@NotifyStudent,0)<>0
BEGIN
update Cert_Register set COMPLETION_DATE=getdate(), STATUS='P', STUDENT_NOTIFIED_DATE=getdate(),
STUDENT_NOTIFIED=1,REQUIREMENT_TYPE=@RequirementType where SEQN=@Seqn
END
if isnull(@NotifyBoard,0)<>0 and isnull(@NotifyStudent,0)=0
BEGIN
update Cert_Register set COMPLETION_DATE=getdate(), STATUS='P', BOARD_NOTIFIED_DATE=getdate(),
BOARD_NOTIFIED=1,REQUIREMENT_TYPE=@RequirementType where SEQN=@Seqn
END
if isnull(@NotifyBoard,0)<>0 and isnull(@NotifyStudent,0)<>0
BEGIN
update Cert_Register set COMPLETION_DATE=getdate(), STATUS='P', BOARD_NOTIFIED_DATE=getdate(),
STUDENT_NOTIFIED_DATE=getdate(),STUDENT_NOTIFIED=1,BOARD_NOTIFIED=1,REQUIREMENT_TYPE=@RequirementType where SEQN=@Seqn
END
select @ActString=''
if isnull(@NotifyBoard,0)<>0
BEGIN
select @ActString='Board notified: '+@CurrentLocalDate
END
if isnull(@NotifyStudent,0)<>0
BEGIN
if datalength(@ActString)>0
BEGIN
select @ActString=@ActString+' Student notified: '+@CurrentLocalDate
END
ELSE
BEGIN
select @ActString='Student notified: '+@CurrentLocalDate
END
END
update Activity set AMOUNT=@TotCharges,EFFECTIVE_DATE=getdate(),ACTION_CODES='Passed',
UNITS=@TotalUnits,NOTE_2=@ActString where SEQN=@ActivitySeqn
select @TotalUnits=0
END
ELSE
BEGIN
select @TreatDeadline=1
END
if isnull(@TreatDeadline,0)=1
BEGIN
select @TreatDeadline=0
if DATEDIFF(day,@Deadline,getdate())>0
BEGIN
if (@AllowGracePeriods>0 and ((@RequirementType=0 and @GracePeriodForInit=1)
or (@RequirementType=1 and @GracePeriodForMaint=1)))
and isnull(@InGracePeriod,0)=0
BEGIN
if @UpdateGoodThruForGracePeriod>0
BEGIN
update Cert_Register set IN_GRACE_PERIOD=1,GOOD_THRU_DATE=dateadd(month,@GracePeriodMonths,@GoodThruDate)-1,
DEADLINE=dateadd(month,@GracePeriodMonths,@Deadline)-1 where SEQN=@Seqn
END
ELSE
BEGIN
update Cert_Register set IN_GRACE_PERIOD=1,DEADLINE=dateadd(month,@GracePeriodMonths,@Deadline)-1 where SEQN=@Seqn
END
END
END
END
END
fetch next from get_rows into
@Seqn,
@StudentID,
@RegistrationItem,
@Status,
@EnrolledDate,
@TotCharges,
@CRNotes,
@Deadline,
@CompletionDate,
@BoardNotified,
@GoodThruDate,
@RequirementType,
@InGracePeriod,
@ActivitySeqn,
@GracePeriodMonths,
@GracePeriodForInit,
@GracePeriodForMaint,
@Designation1,
@Designation2,
@Designation3,
@JointProgram,
@AutoEnrollForMaint,
@RotateDateRule,
@RotateBeginDate,
@RotateCompletionMonths,
@FullName,
@Company,
@City,
@StateProvince,
@Country,
@CoID,
@MemberType,
@ProgramID,
@Title,
@ProgramType
END
close get_rows
deallocate get_rows
declare treat_results cursor for
select * from #CompletedList order by PROGRAM
open treat_results
fetch next from treat_results into @StudentID,@CompletionDate,@ProgramID,@FullName,@Company,@City,
@StateProvince,@Country,@Seqn,@ActivitySeqn,@InGracePeriod,@AutoEnrollForMaint,@ProgramType,
@RotateDateRule,@RotateBeginDate,@RotateCompletionMonths,@Designation1,@Designation2,@Designation3,
@GracePeriodForInit,@GracePeriodForMaint,@Deadline,@GoodThruDate,@CoID,@MemberType,@TotCharges,@CRNotes,
@RequirementType,@Title
WHILE @@FETCH_STATUS = 0
BEGIN
if isnull(@AutoEnrollForMaint,0)<>0 and isnull(@ProgramType,0)<>0
BEGIN
exec @Result=sp_iboGetCounter 'Activity',1,@NewActSeqn output
exec @Result=sp_iboGetCounter 'Cert_Register',1,@NewProgSeqn output
if @GoodThruDate is not null
BEGIN
select @EnrolledDate=@GoodThruDate+1
END
If @RequirementType=0
BEGIN
if @RotateDateRule=0
BEGIN
select @Deadline=@RotateBeginDate
END
If @RotateDateRule=1
BEGIN
select @Deadline=dateadd(month,@RotateCompletionMonths,@EnrolledDate)-1
END
If @RotateDateRule>1
BEGIN
select @Deadline=null
END
END
If @RequirementType<>0
BEGIN
if @RotateDateRule=0
BEGIN
select @Deadline=dateadd(month,@RotateCompletionMonths,@RotateBeginDate)-1
WHILE @Deadline<=@EnrolledDate and @RotateCompletionMonths>0
BEGIN
select @Deadline= dateadd(month,@RotateCompletionMonths,@Deadline)
END
END
if @RotateDateRule=1
BEGIN
select @Deadline=dateadd(month,@RotateCompletionMonths,@EnrolledDate)-1
END
if @RotateDateRule>1
BEGIN
select @Deadline=null
END
END
if @Deadline is not null
BEGIN
select @GoodThruDate=@Deadline
END
begin transaction
insert into Cert_Register (SEQN,ENROLLED_DATE,STATUS,REG_TYPE,STUDENT_ID,BT_ID,REGISTRATION_ITEM,
REQUIREMENT_TYPE,DESCRIPTION,DEADLINE,GOOD_THRU_DATE,ACTIVITY_SEQN)
values(@NewProgSeqn,@EnrolledDate,'E', 'P',@StudentID,@StudentID,@RegistrationItem,1,
@Title+' - Maintenance',@Deadline,@GoodThruDate,@NewActSeqn)
insert into Activity (SEQN,ID,NOTE,ACTIVITY_TYPE,SOURCE_SYSTEM,PRODUCT_CODE,TRANSACTION_DATE,
DESCRIPTION,AMOUNT,MEMBER_TYPE,UF_1,UF_2,UF_3,UF_4,UF_5,UF_6,UF_7,CO_ID,EFFECTIVE_DATE,THRU_DATE,
ACTION_CODES,CATEGORY)
values (@NewActSeqn,@StudentID,@CRNotes,'CERTIFICAT','CERTIFICAT',@ProgramID,@EnrolledDate,
@Title+' - Maintenance',@TotCharges,@MemberType,@ActUF1,@ActUF2,@ActUF3,@ActUF4,@ActUF5,@ActUF6,@ActUF7,
@CoID,NULL,@GoodThruDate,'Enrolled','Maint')
commit transaction
END
fetch next from treat_results into @StudentID,@CompletionDate,@ProgramID,@FullName,@Company,@City,
@StateProvince,@Country,@Seqn,@ActivitySeqn,@InGracePeriod,@AutoEnrollForMaint,@ProgramType,
@RotateDateRule,@RotateBeginDate,@RotateCompletionMonths,@Designation1,@Designation2,@Designation3,
@GracePeriodForInit,@GracePeriodForMaint,@Deadline,@GoodThruDate,@CoID,@MemberType,@TotCharges,@CRNotes,
@RequirementType,@Title
END
close treat_results
deallocate treat_results
select
STUDENT_ID,
COMPLETION_DATE,
PROGRAM,
FULLNAME,
COMPANY,
CITY,
STATE_PROVINCE,
COUNTRY,
Designation1,
Designation2,
Designation3
from #CompletedList
order by PROGRAM
GO
GRANT EXECUTE ON [dbo].[sp_asi_Cert_Completion] TO [IMIS]
GO
[dbo].[sp_asi_Cert_Completion]